﻿use Workflow
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('wujiajun','6','2','6')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('wujiajun','6','2','7')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('wujiajun','6','2','8')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('wujiajun','6','2','9')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('wujiajun','6','2','10')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('wujiajun','6','2','15')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('wujiajun','6','2','16')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('wujiajun','6','2','17')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('wujiajun','6','2','18')





use Workflow
--update WorkflowStatuses set CompleteTime=getdate() where WorkParentId=13
--select Email from UserInfoes where UserName='ligaoshen'

DROP TABLE WorkflowChildLog
drop table WorkfolwLog


use Workflow
select Number from BaoJia where Id=(select Max(Id) from BaoJia)

use MQC
select * from [Check] where Maktx='21012626'
update [Check] set Maktx='21012626' where Maktx='F00VE21013'

use workflow
select top 1 * from GetNum where Business='塑模' order by Id desc

use workflow
select * from WorkflowStatuses inner join WorkflowChildStatuses on WorkflowStatuses.WorkParentId=WorkflowChildStatuses.WorkParentId left join Bugs on WorkflowStatuses.WorkParentId=Bugs.WorkParentId where nodeName='审核提报' and WorkflowStatuses.StatusDefine>=-1 and WorkflowStatuses.StatusDefine<=1 and WorkflowChildStatuses.StatusNo=1

use workflow
select * from WorkflowStatuses inner join WorkflowChildStatuses on WorkflowStatuses.WorkParentId=WorkflowChildStatuses.WorkParentId left join BaoJia on WorkflowStatuses.WorkParentId=BaoJia.WorkParentId where WorkflowStatuses.CreateTime >='2017-1-1'

use workflow
select * from workflowlog where WorkParentId='248' or  WorkParentId='249' or  WorkParentId='250' or  WorkParentId='251' or  WorkParentId='252'

use workflow
select * from [View]
use workflow
Select  top 1 * from UserInfoes order by UserName desc

use mqc
select * from Cavity where mold='M11091'

select * from Record where Mold='M11091'
select * from Record_Cavity where Batch='M11091_21007793_20170731_5680'

use Workflow
select * from BaoJia where WorkParentId='1'
use Workflow






use Workflow
select * from WorkflowChildMoulds


use Workflow
declare @sql varchar(8000)

select @sql= isnull(@sql+ '],[' , '') + NodeName from PJNodes groupbyNodeName

set @sql= '[' + @sql + ']'

exec ('select * from (select * from PJNodes) a pivot (max(Number)for NodeName in (' + @sql + ')) b')

select * from (select * from PJNodes) a pivot(max(CompleteDate,RealCompleteDate)for NodeName in(FOT,OTS))b


use workflow

SELECT t1.PJId,DisplayName,PJMains.PJType,PJMains.MoldType,t1.[FOT] as [P1],t1.[OTS] as [P2],t1.[T/R] as [P3],t1.[PPAP] as [P4],t1.[进入爬坡] as [P5],t1.[退出爬坡] as [P6],t2.[FOT] as [R1],t2.[OTS] as [R2],t2.[T/R] as [R3],t2.[PPAP] as [R4],t2.[进入爬坡] as [R5],t2.[退出爬坡] as [R6],PJLogs.Problem,t1.Ps FROM
	 (SELECT * FROM 
		(select PJId,CompleteDate,NodeName,Statues,WorkParentId,Ps from PJNodes) AS A  pivot( max(CompleteDate) for NodeName in([FOT],[OTS],[T/R],[PPAP],[进入爬坡],[退出爬坡])) AS P) 
		as t1 
		inner join WorkflowStatuses on WorkflowStatuses.WorkParentId=t1.WorkParentId 
		inner join UserInfoes on UserInfoes.UserName=WorkflowStatuses.CreaterUserName
		inner join PJMains on t1.PJId=PJMains.PJId and PJMains.Statues='1'
		left join PJLogs on PJLogs.PJId=t1.PJId and PJLogs.Id=(select max(id) from PJLogs where PJLogs.PJId =t1.PJId and PJLogs.Statues='1')
		, 
	(SELECT * FROM 
		(select PJId,NodeName,RealCompleteDate from PJNodes) AS A pivot( max(RealCompleteDate) for NodeName in([FOT],[OTS],[T/R],[PPAP],[进入爬坡],[退出爬坡])) AS P1 )
		 as t2 
		 
		where t1.Pjid = t2.Pjid and DisplayName='陆凯霄' and t1.Statues='1'
use Workflow

select * from PJNodes where PJId='A17005'
select * from PJMains where PJId='A17005'

use workflow
select * from NodeFOT where PJId = 'M17030' and CompleteDate>='2017-01-01' and CompleteDate<='2018-01-01' union all
select * from NodeOTS where PJId = 'M17030' and CompleteDate>='2017-01-01' and CompleteDate<='2019-01-01' union all
select * from NodePPAP where PJId = 'M17030' and CompleteDate>='2017-01-01' and CompleteDate<='2019-01-01'


use workflow


insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('shenyugang','6','2','6')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('shenyugang','6','2','7')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('shenyugang','6','2','8')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('shenyugang','6','2','9')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('shenyugang','6','2','10')

use workflow
select * from PJOrderChildStarts where serialnum='10000010' or serialnum='10100012' or serialnum='11300003'

select * from PJOrderStarts where serialnum='10000010'

use workflow
select COUNT(*) from PJOrderStarts where WorkParentId='1801'

use workflow
select * from PJOrderStarts inner join PJOrderChildStarts on PJOrderChildStarts.SerialNum = PJOrderStarts.SerialNum order by WorkParentId asc
select * from WorkflowLog where WorkParentId='830'
select * from WorkflowLog where WorkParentId='829'

use Workflow

select * from WorkflowLog where WorkParentId='1290'

use workflow
select * from PJSampleOuts left join PJSampleChildOuts
on PJSampleOuts.SerialNum=PJSampleChildOuts.SerialNum
where Statues='1'

use DB_IMG290
select count(*) from T_Data_2017

use workflow
select SerialNum from PJSampleOuts
where Id=(select Max(Id) from PJSampleOuts
where OrderType='收费订单' and right(OrderStartSerialNum,2)='07')
use workflow
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('xuhuijing','6','2','17')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('zhaoqiang','6','2','17')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('fanjiazhu','6','2','17')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('chendongwei','6','2','17')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('shenyugang','6','2','17')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('lukaixiao','6','2','17')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('chuxin','6','2','17')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('xuhuijing','6','17','17')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('hejiaping','6','22','17')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('xuhuijing','6','2','18')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('zhaoqiang','6','2','18')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('fanjiazhu','6','2','18')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('chendongwei','6','2','18')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('shenyugang','6','2','18')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('lukaixiao','6','2','18')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('chuxin','6','2','18')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('xuhuijing','6','17','18')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('hejiaping','6','22','18')
insert into UserTeamRoles(UserName,TeamId,RoleId,WorkMouldId) values('jiangyihuan','6','20','18')

use workflow

select * from PJSampleOuts where SerialNum = '1336'
use workflow
select * from PJOrderStarts where WorkparentId = '1336'
use workflow
select * from PJNodes where PJId = 'M17005'
select * from PJOrderStarts where SerialNum = '10100045'


use workflow
select * from PJMains where T2EndDate - getdate()>=22 and T2EndDate - getdate()<=23


select CONVERT(varchar(12) , getdate(), 111 ) 

use workflow
select *  from PJMains left join PJLogs on PJLogs.PJId=PJMains.PJId and PJLogs.Id=(select max(id) from PJLogs where PJLogs.PJId =PJMains.PJId) where PJMains.PJId ='M123456'


